package com.hp.tools.db;

import com.hp.tools.model.Column;
import com.hp.tools.model.Key;
import com.hp.tools.model.KeyInfo;
import com.hp.tools.model.Table;
import org.apache.commons.collections4.CollectionUtils;

import java.sql.*;
import java.util.*;

/**
 * 获取数据库连接
 *
 * @author hupan
 */
public class Mysql extends DB {

	private Mysql() {

	}

	private static class SingleTonHoler {
		private static final Mysql INSTANCE = new Mysql();
	}

	/**
	 * 单例
	 */
	public static Mysql getInstance() {
		return SingleTonHoler.INSTANCE;
	}

	/**
	 * 执行sql
	 */
	@Override
	public ResultSet executeQuery(String sql) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			conn = getConnection();
			stmt = conn.createStatement();
			if (stmt != null) {
				rs = stmt.executeQuery(sql);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll(conn, stmt, rs);
		}

		return rs;

	}

	/**
	 * 获取数据库中所有表的表名，并添加到列表结构中
	 */
	@Override
	public List<String> getTableNameList() {
		List<String> tableNameList = new ArrayList<>();

		DatabaseMetaData dbmd = null;
		Connection conn = null;
		try {
			conn = Mysql.getConnection();
			dbmd = conn.getMetaData();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		if (dbmd != null) {
			//访问当前用户下的所有表
			ResultSet rs = null;
			try {
				rs = dbmd.getTables(conn.getCatalog(), username, "%", new String[]{"TABLE"});
				while (rs.next()) {
					tableNameList.add(rs.getString("table_name"));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				closeAll(conn, null, rs);
			}
		}

		return tableNameList;

	}

	/**
	 * 从数据库中获取指定条件的表名
	 */
	@Override
	public List<Table> getAllTables() {
		List<Table> tableList = new ArrayList<>();

		Connection conn = null;
		Statement statement = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			statement = conn.createStatement();
			rs = statement.executeQuery("select table_name,table_comment from information_schema.tables where table_schema='" + db + "'");

			while (rs.next()) {
				Table table = new Table();
				table.setTableName(rs.getString("table_name"));
				table.setTableComment(rs.getString("table_comment"));
				tableList.add(table);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll(conn, statement, rs);
		}

		tableList.sort((o1, o2) -> o1.getTableName().compareToIgnoreCase(o2.getTableName()));

		return tableList;
	}

	/**
	 * 获取数据表中所有列的列名，并添加到列表结构中
	 */
	@Override
	public List<String> getColumnNameList(String tableName) {
		List<String> columnNameList = new ArrayList<>();

		Connection conn = null;
		DatabaseMetaData dbmd = null;
		try {
			conn = getConnection();
			dbmd = conn.getMetaData();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}

		if (dbmd != null) {
			ResultSet rs = null;
			try {
				rs = dbmd.getColumns(conn.getCatalog(), "%", tableName, "%");
				while (rs.next()) {
					columnNameList.add(rs.getString("column_name"));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				closeAll(conn, null, rs);
			}
		}

		columnNameList.sort(String::compareToIgnoreCase);

		return columnNameList;
	}


	/**
	 * 根据表名获取表结构信息
	 */
	@Override
	public List<Column> getStructOfTable(String tableName) {
		List<Column> list = new ArrayList<>();
		StringBuilder sql = new StringBuilder();

		sql.append("SELECT column_name,data_type,character_maximum_length data_length,numeric_precision data_precision,numeric_scale data_Scale,is_nullable nullable,column_default data_default,column_comment FROM information_schema.columns ");
		sql.append("WHERE table_name=? and table_schema=?");

		ResultSet rs = null;

		try (Connection conn = getConnection(); PreparedStatement pstm = conn.prepareStatement(sql.toString())) {
			pstm.setString(1, tableName);
			pstm.setString(2, db);
			rs = pstm.executeQuery();

			while (rs.next()) {
				Column entity = new Column();
				entity.setColumnName(rs.getString("column_name"));

				String dataType = rs.getString("data_type");
				long dataLength = rs.getLong("data_length");
				int dataPrecision = rs.getInt("data_precision");
				int dataScale = rs.getInt("data_Scale");

				if ("NUMBER".equalsIgnoreCase(dataType) || "tinyint".equalsIgnoreCase(dataType) || "smallint".equalsIgnoreCase(dataType) || "mediumint".equalsIgnoreCase(dataType) || "int".equalsIgnoreCase(dataType) || "integer".equalsIgnoreCase(dataType)
						|| "bigint".equalsIgnoreCase(dataType)) {
					entity.setDataType(dataType + "(" + dataPrecision + ")");
				} else if ("float".equalsIgnoreCase(dataType) || "double".equalsIgnoreCase(dataType) || "decimal".equalsIgnoreCase(dataType)) {
					entity.setDataType(dataType + "(" + dataPrecision + "," + dataScale + ")");
				} else if ("TIMESTAMP(6)".equalsIgnoreCase(dataType)) {
					entity.setDataType(dataType);
				} else {
					entity.setDataType(dataType + "(" + dataLength + ")");
				}

				entity.setDataLength(dataLength);
				entity.setDataPrecision(dataPrecision);
				entity.setDataScale(rs.getInt("data_Scale"));
				entity.setNullable(rs.getString("nullable"));
				entity.setDataDefault(rs.getString("data_default"));
				entity.setColumnComments(rs.getString("column_comment"));

				list.add(entity);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return list;
	}


	/**
	 * 根据表名获取索引信息
	 */
	@Override
	public List<KeyInfo> getIndex(String tableName) {
		List<Key> list = new ArrayList<>();
		StringBuilder sql = new StringBuilder();
		sql.append("SHOW KEYS FROM `").append(tableName).append("`");

		try (Connection conn = getConnection(); PreparedStatement pstm = conn.prepareStatement(sql.toString()); ResultSet rs = pstm.executeQuery()) {

			while (rs.next()) {
				Key key = new Key();
				key.setTable(rs.getString("Table"));
				key.setNonUnique(rs.getInt("Non_unique"));
				key.setKeyName(rs.getString("Key_name"));
				key.setSeqInIndex(rs.getInt("Seq_in_index"));
				key.setColumnName(rs.getString("Column_name"));
				key.setCollation(rs.getString("Collation"));
				key.setCardinality(rs.getInt("Cardinality"));
				key.setSubPart(rs.getString("Sub_part"));
				key.setPacked(rs.getString("Packed"));
				key.setNullable(rs.getString("Null"));
				key.setIndexType(rs.getString("Index_type"));
				key.setComment(rs.getString("Comment"));
				key.setIndexComment(rs.getString("Index_comment"));

				list.add(key);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		if (CollectionUtils.isEmpty(list)) {
			return Collections.emptyList();
		}

		Map<String, KeyInfo> keyMap = new TreeMap<>();

		list.forEach(e -> {
			if (!keyMap.containsKey(e.getKeyName())) {
				keyMap.put(e.getKeyName(), KeyInfo.ofKey(e));
				return;
			}

			KeyInfo keyInfo = keyMap.get(e.getKeyName());
			keyInfo.setColumns(keyInfo.getColumns() + ", " + e.getColumnName());
		});

		return new ArrayList<>(keyMap.values());
	}

	public static void main(String[] args) {
		Mysql mysql = new Mysql();
		List<KeyInfo> keys = mysql.getIndex("ti_computer");

		System.out.println(keys);
	}

}